﻿using PeiYue.Common;
using PeiYue.IRepository.Base;
using PeiYue.IServices.Purchase;
using PeiYue.Model.Dto.Purchase;
using PeiYue.Model.ERPModels;
using PeiYue.Services.BASE;
using SqlSugar;
using StackExchange.Redis;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace PeiYue.Services.Purchase
{
    public class PurchaseOrderDetail : BaseServices<ErpPurchaseOrderDetail>, IPurchaseOrderDetail
    {
        IBaseRepository<ErpPurchaseOrderDetail> _dal;
        public PurchaseOrderDetail(IBaseRepository<ErpPurchaseOrderDetail> dal)
        {
            this._dal = dal;
            base.BaseDal = dal;
        }

        public async Task<List<ErpPurchaseOrderDetail>> GetERPSuppPurchase(SearchERPPurchaseDetailDTO querymodel)
        {
            StringBuilder sqlWhere = new StringBuilder("");
            sqlWhere.Append(string.IsNullOrEmpty(querymodel.CustomerCode) ? "" : " AND  ISNULL( l.客户编号,'自拣')=@CustomerCode");
            sqlWhere.Append(string.IsNullOrEmpty(querymodel.SkuCode) ? "" : " AND  l.存货编码=@SkuCode");
            sqlWhere.Append(string.IsNullOrEmpty(querymodel.SupplierCode) ? "" : " AND h.供应商编号=@SupplierCode");
            if (!string.IsNullOrEmpty(querymodel.Supplier))
            {
                querymodel.Supplier = ("%" + querymodel.Supplier + "%");
                sqlWhere.Append(" AND (h.供应商编号 like @Supplier OR h.供应商 like @Supplier)");
            }
            sqlWhere.Append(string.IsNullOrEmpty(querymodel.SupplierMarket) ? "" : " AND  h.供应商市场=@SupplierMarket");
            //AND  l.要求送货日期 =@DeliveryDate '2020-08-18'AND 销售ID>0,r.Flag,r.Qty, Case when r.PackQty>0 then  r.PackQty else 1 end as PackQty
            //left join [OMS].[dbo].oms_srm_sorting_record r on r.FID=l.[fID]  AND l.销售ID>0

            string date = (DateTime.Now.Hour >= int.Parse(Appsettings.app(new string[] { "QueryHour", "SupplierHour" })) ? DateTime.Now.AddDays(1).ToShortDateString() : (DateTime.Now.Hour <= int.Parse(Appsettings.app(new string[] { "QueryHour", "ReceiptHour" })) ? DateTime.Now.ToShortDateString() : DateTime.Now.AddDays(30).ToShortDateString()));

            return await _dal.SqlQuery(@$" select   CASE WHEN h.订单状态 in('待报货','待收货') THEN 1 ELSE 0 END '状态',
l.fID, ISNULL( l.客户编号,'自拣')客户编号,l.客户ID ,h.要求送货日期 ,h.供应商市场,h.供应商 SupplierName, h.供应商编号 SupplierCode,isnull(l.行号,0) as LineNumber,
 l.存货编码 , (case when l.品名 like '新品%' then l.品名+' '+ISNULL(销售备注,'') else l.品名 end )as 品名 ,l.[采购单价] ,l.采购计量 ,l.[采购数量] ,l.[金额] ,l.订购数量,l.报货金额
,ISNULL(CAST(l.销售ID AS varchar(20)),('0'+CAST( l.fID AS varchar(20)))) 销售ID  , ISNULL( l.[加工要求],'') [加工要求],ISNULL(l.[客户优先级],'') [客户优先级]  
 from [供应链].[dbo].[v采购订单表体] l 
 left join [供应链].[dbo].[v采购订单表头] h on l.[单号]=h.[单号]
 where  h.要求送货日期= '{date}'
AND h.订单状态 in('待报货','待收货','待付款') 
AND h.类型 in ('正常订单','补货订单') AND l.[采购数量]>0  and l.是否附随品='否'  
 {sqlWhere}
 order by   l.[客户优先级] desc ,l.客户ID, l.存货编码", new { querymodel.DeliveryDate, querymodel.SupplierCode, querymodel.CustomerCode, querymodel.SkuCode, querymodel.SupplierMarket, querymodel.Supplier });//r.Flag,  AND h.订单状态 in('待报货','待收货') 
        }

        public async Task<ERPPurchaseDetailDTO> GetPurchaseDetailByBarCode(string barCode)
        {
            List<SugarParameter> sugarParameters = new List<SugarParameter>();
            sugarParameters.Add(new SugarParameter("@barCode", barCode));
            var list = await _dal.SqlQueryList<ERPPurchaseDetailDTO>(@$" select top 1 l.fID,h.供应商编号 SupplierCode
                     from [供应链].[dbo].[v采购订单表体] l 
                     left join [供应链].[dbo].[v采购订单表头] h on l.[单号]=h.[单号] 
                     left join [供应链].[dbo].[v仓库拣选日志] m on   m.采购ID=L.fID
                     where m.条码=@barCode  ", sugarParameters);
            return list.FirstOrDefault();
        }
        public async Task<ERPPurchaseDetailDTO> GetPurchaseDetailBySaleId(int saleId)
        {
            List<SugarParameter> sugarParameters = new List<SugarParameter>();
            sugarParameters.Add(new SugarParameter("@saleId", saleId));
            var list = await _dal.SqlQueryList<ERPPurchaseDetailDTO>(@$" select top 1 l.fID,h.供应商编号 SupplierCode
                     from [供应链].[dbo].[v采购订单表体] l 
                     left join [供应链].[dbo].[v采购订单表头] h on l.[单号]=h.[单号] 
                     where l.销售ID=@saleId  ", sugarParameters);
            return list.FirstOrDefault();
        }
        /// <summary>
        /// 获取供应商收货单
        /// </summary>
        /// <param name="supplierCode"></param>
        /// <param name="deliveryDate"></param>
        /// <returns></returns>
        public async Task<List<ReceivingGoodsDTO>> GetReceivingGoodsList1(string supplierCode,DateTime deliveryDate)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select b.品名 as SkuName,(isnull(b.客户优先级,'')+b.客户编号) as CustomerCode,isnull(b.报货数,采购数量) as Qty,  "); 
            strSql.Append("b.单价 as Price, b.金额 as [Money],b.规格 as Spec,h.日期 as DeliveryDate ");
            strSql.Append(" from 供应链.dbo.v采购订单表头 h ");
            strSql.Append(" inner join 供应链.dbo.v采购订单表体 b on h.单号 = b.单号  ");
            strSql.Append(" where h.日期=@DeliveryDate and h.供应商编号=@SupplierCode and h.类型='正常订单'  ");
            List<SugarParameter> sugarParameters = new List<SugarParameter>();
            sugarParameters.Add(new SugarParameter("@SupplierCode", supplierCode));
            sugarParameters.Add(new SugarParameter("@DeliveryDate", deliveryDate));
            return await _dal.SqlQueryList<ReceivingGoodsDTO>(strSql.ToString(), sugarParameters);
        }
        /// <summary>
        /// 获取供应商收货单
        /// </summary>
        /// <param name="supplierCode"></param>
        /// <param name="deliveryDate"></param>
        /// <returns></returns>
        public async Task<ReceivingGoodsDTO> GetReceivingGoodsList(string supplierCode, DateTime deliveryDate)
        {
            ReceivingGoodsDTO dto = new ReceivingGoodsDTO();
            dto.NormalOrderList = new List<OrderList>();
            var orderList = await GetOrderList(supplierCode, deliveryDate);
            foreach (var item in orderList)
            {
                dto.NormalOrderList.Add(item);
                item.OrderDetail = await GetOrderDetail(item.OrderId);
                item.TotalMoney += item.OrderDetail.Sum(t => t.Money);
            }
            dto.AddOrderList = new List<OrderList>();
            orderList = await GetOrderList(supplierCode, deliveryDate,"补货订单");
            foreach (var item in orderList)
            {
                dto.AddOrderList.Add(item);
                item.OrderDetail = await GetOrderDetail(item.OrderId);
                item.TotalMoney += item.OrderDetail.Sum(t => t.Money);
            }
            dto.BackOrderList = new List<OrderList>();
            orderList = await GetOrderList(supplierCode, deliveryDate, "全额退货");
            foreach (var item in orderList)
            {
                dto.BackOrderList.Add(item);
                item.OrderDetail = await GetOrderDetail(item.OrderId);
                item.TotalMoney += item.OrderDetail.Sum(t => t.Money);
            }
            return dto;
        }
        /// <summary>
        /// 获取订单表头
        /// </summary>
        /// <param name="supplierCode"></param>
        /// <param name="deliveryDate"></param>
        /// <param name="orderType"></param>
        /// <returns></returns>
        public async Task<List<OrderList>> GetOrderList(string supplierCode, DateTime deliveryDate,string orderType="正常订单")
        {
            StringBuilder strSql = new StringBuilder();
            List<SugarParameter> sugarParameters = new List<SugarParameter>();
            strSql.Append("select 单号 as OrderId,供应商编号 as SupplierCode,供应商 as SupplierName,要求送货日期 as DeliveryDate,类型 from v采购订单表头 ");
            strSql.Append("where 供应商编号=@SupplierCode ");
            if (orderType == "正常订单")
            {
                strSql.Append(" and 日期>=@DeliveryDate ");
            }
            else if (orderType == "补货订单")
            {
                strSql.Append(" and DateDiff(dd, 要求送货日期, @DeliveryDate)= 1 ");
            }
            else if (orderType == "全额退货")
            {
                strSql.Append(" and DateDiff(dd, 要求送货日期, @DeliveryDate)= 1 ");
            }
            strSql.Append(" and 类型 =@OrderType ");
            strSql.Append(" and 订单状态 in('待付款' ,'已成交') ");
            strSql.Append(" group by 单号,供应商编号,供应商,要求送货日期,类型 ");
            sugarParameters.Add(new SugarParameter("@DeliveryDate", deliveryDate));
            sugarParameters.Add(new SugarParameter("@SupplierCode", supplierCode));
            sugarParameters.Add(new SugarParameter("@OrderType", orderType));
            return await _dal.SqlQueryList<OrderList>(strSql.ToString(), sugarParameters);
        }
        /// <summary>
        /// 获取采购订单详情
        /// </summary>
        /// <param name="orderId"></param>
        /// <returns></returns>
        public async Task<List<OrderDetail>> GetOrderDetail(string orderId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select c.简称 as SkuName,(isnull(b.客户优先级,'')+b.客户编号) as CustomerCode,isnull(b.收货数,0) as Qty,  ");
            strSql.Append("b.单价 as Price, b.收货金额 as [Money],b.规格 as Spec,h.日期 as DeliveryDate ");
            strSql.Append(" from 供应链.dbo.v采购订单表头 h ");
            strSql.Append(" inner join 供应链.dbo.v采购订单表体 b on h.单号 = b.单号  ");
            strSql.Append(" Left Join 供应链.dbo.v存货档案 (Nolock)  c ON b.存货ID = c.fID ");
            strSql.Append(" where  h.单号=@OrderId  ");
            List<SugarParameter> sugarParameters = new List<SugarParameter>();
            sugarParameters.Add(new SugarParameter("@OrderId", orderId));
            return await _dal.SqlQueryList<OrderDetail>(strSql.ToString(), sugarParameters);
        }
    }

}
